﻿using Datav.Design.Datas.Pocos;
using ICSharpCode.SharpZipLib.Zip;
using XCode.DataAccessLayer;

namespace Datav.Design.CmdExecutor
{
    /// <summary>
    ///
    /// </summary>
    public class BackupSQLService
    {
        /// <summary>
        ///
        /// </summary>
        /// <param name="caseProjecId"></param>
        public static void BackupMain(long caseProjecId)
        {
            string sourceConnectionString = Project.Meta.ConnName; // 替换为源数据库连接字符串
            string _dbFileDirectory = AppContext.BaseDirectory + "data" + "\\" + caseProjecId;
            string backupTablePath = _dbFileDirectory + "\\" + "table_backup.sql"; // 生成的 SQL 文件路径
            string backupDatasPath = _dbFileDirectory + "\\" + "datas_backup.sql"; // 生成的 SQL 文件路径

            if (!Directory.Exists(_dbFileDirectory))
                Directory.CreateDirectory(_dbFileDirectory);

            //if (System.IO.File.Exists(filePath))
            //{
            //    // 替换旧文件
            //    System.IO.File.Delete(filePath);
            //}
            // 获取源数据库中所有的表
            List<string> tableNames = GetTableNames(sourceConnectionString);

            // 创建并打开 SQL 文件,生成 CREATE TABLE SQL
            using (StreamWriter writer = new StreamWriter(backupTablePath))
            {
                // 为每个表生成备份 SQL 并写入文件
                foreach (var tableName in tableNames)
                {
                    //GenerateBackupSQL(sourceConnectionString, tableName, writer, caseProjecId);
                    // 生成 CREATE TABLE SQL
                    string createTableSQL = GenerateCreateTableSQL(sourceConnectionString, tableName);
                    if (!string.IsNullOrEmpty(createTableSQL))
                    {
                        writer.WriteLine(createTableSQL);
                        writer.WriteLine(); // 分隔每个表的结构和数据
                    }
                }
            }

            // 创建并打开 SQL 文件
            using (StreamWriter writer = new StreamWriter(backupDatasPath))
            {
                // 为每个表生成备份 SQL 并写入文件
                foreach (var tableName in tableNames)
                {
                    GenerateBackupSQL(sourceConnectionString, tableName, writer, caseProjecId);
                }
            }

            //Console.WriteLine($"所有表的备份 SQL 已生成，文件路径：{backupFilePath}");
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="path"></param>
        public static void DeleteDirectory(string path)
        {
            if (Directory.Exists(path))
            {
                // 删除目录及其中的所有文件和子目录
                foreach (var file in Directory.GetFiles(path))
                {
                    File.Delete(file);  // 删除文件
                }

                foreach (var dir in Directory.GetDirectories(path))
                {
                    DeleteDirectory(dir);  // 递归删除子目录
                }

                Directory.Delete(path, true);  // 删除空目录
                Console.WriteLine($"Directory '{path}' and all its contents have been deleted.");
            }
            else
            {
                Console.WriteLine($"Directory '{path}' does not exist.");
            }
        }

        // 获取源数据库中的所有表名
        private static List<string> GetTableNames(string connectionString)
        {
            List<string> tableNames = new List<string>();
            var dal = DAL.Create(Project.Meta.ConnName);
            string query = $"SELECT table_name FROM information_schema.tables WHERE TABLE_SCHEMA = '{connectionString}'";
            var reader = dal.ExecuteReader(query);
            while (reader.Read())
            {
                tableNames.Add(reader.GetString(0));
            }

            return tableNames;
        }

        // 为每个表生成备份 SQL 并写入文件
        private static void GenerateBackupSQL(string connectionString, string tableName, StreamWriter writer, long caseProjecId)
        {
            // 获取表结构（列名）
            List<string> columnNames = GetColumnNames(connectionString, tableName);
            if (columnNames.Count == 0) return;
            string selectQuery = "";
            if (tableName == "case_configs")
            {
                selectQuery = $"SELECT * FROM {tableName} where Id={caseProjecId};";
            }
            else
            {
                // 生成查询源数据的 SQL
                selectQuery = $"SELECT * FROM {tableName} where ProjectId={caseProjecId};";
            }
            // 获取源数据
            List<List<object>> rows = GetDataFromSource(connectionString, selectQuery);

            if (rows.Count == 0) return;

            // 生成插入 SQL
            string insertQuery = GenerateInsertQuery(tableName, columnNames, rows);

            // 写入 SQL 文件
            writer.WriteLine(insertQuery);
            writer.WriteLine();  // 每个表之间留一个空行
        }

        // 生成 MySQL 的 CREATE TABLE SQL
        private static string GenerateCreateTableSQL(string connectionString, string tableName)
        {
            List<string> columnDefinitions = new List<string>();
            var dal = DAL.Create(Project.Meta.ConnName);
            string query = $"SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, IS_NULLABLE, COLUMN_DEFAULT " +
                            $"FROM INFORMATION_SCHEMA.COLUMNS " +
                            $"WHERE TABLE_NAME = '{tableName}' and TABLE_SCHEMA = '{connectionString}' ";
            var reader = dal.ExecuteReader(query);

            while (reader.Read())
            {
                string columnName = reader.GetString(0);
                string dataType = reader.GetString(1);
                string length = ""; // Initialize length as empty string
                string precision = reader.IsDBNull(3) ? "" : reader.GetInt32(3).ToString();
                string scale = reader.IsDBNull(4) ? "" : reader.GetInt32(4).ToString();
                string isNullable = reader.GetString(5);
                string defaultValue = reader.IsDBNull(6) ? "" : reader.GetString(6);

                // 转换数据类型为 MySQL 格式
                if (dataType == "nvarchar" || dataType == "varchar")
                {
                    dataType = "VARCHAR";
                }
                else if (dataType == "int" || dataType == "integer")
                {
                    dataType = "INT";
                }
                else if (dataType == "datetime" || dataType == "smalldatetime")
                {
                    dataType = "DATETIME";
                }
                else if (dataType == "decimal" || dataType == "numeric")
                {
                    dataType = "DECIMAL";
                }
                else if (dataType == "bit" || dataType == "boolean")
                {
                    dataType = "TINYINT(1)"; // MySQL doesn't have boolean type; TINYINT(1) is used instead
                }
                else if (dataType == "text")
                {
                    dataType = "TEXT";
                }
                else if (dataType == "date")
                {
                    dataType = "DATE";
                }
                else if (dataType == "time")
                {
                    dataType = "TIME";
                }
                else if (dataType == "char")
                {
                    dataType = "CHAR";
                }
                else if (dataType == "binary")
                {
                    dataType = "BINARY";
                }
                else if (dataType == "longtext")
                {
                    dataType = "LONGTEXT";
                }
                else if (dataType == "tinyint")
                {
                    dataType = "TINYINT";
                }
                else if (dataType == "bigint")
                {
                    dataType = "BIGINT";
                }
                // 如果是其他未处理的类型，保留原样
                else
                {
                    dataType = dataType.ToUpper();
                }

                string columnDefinition = $"{columnName} {dataType}";

                // 处理字符类型的最大长度 (仅 VARCHAR 和 CHAR 类型需要处理长度)
                if ((dataType == "VARCHAR" || dataType == "CHAR") && !reader.IsDBNull(2))
                {
                    length = reader.GetInt32(2).ToString(); // 获取最大长度
                    columnDefinition += $"({length})";
                }
                // 对于 TEXT 和 LONGTEXT 不需要设置长度
                else if (dataType == "TEXT" || dataType == "LONGTEXT")
                {
                    // 无需处理长度
                }

                // 处理精度（仅针对 DECIMAL 类型）
                if (!string.IsNullOrEmpty(precision) && dataType == "DECIMAL")
                {
                    columnDefinition += $"({precision}, {scale})";
                }

                // 处理可空性
                if (isNullable == "NO")
                {
                    columnDefinition += " NOT NULL";
                }
                else
                {
                    columnDefinition += " NULL";
                }

                // 默认值
                if (!string.IsNullOrEmpty(defaultValue))
                {
                    // 如果是字符串类型的默认值，加引号
                    if (defaultValue.StartsWith("'") && defaultValue.EndsWith("'"))
                    {
                        columnDefinition += $" DEFAULT {defaultValue}";
                    }
                    else
                    {
                        columnDefinition += $" DEFAULT {defaultValue}";
                    }
                }

                // 处理自增主键
                if (columnName == "id" && dataType == "INT" && isNullable == "NO")
                {
                    columnDefinition += " AUTO_INCREMENT";
                }

                columnDefinitions.Add(columnDefinition);
            }

            if (columnDefinitions.Count == 0) return string.Empty;

            // MySQL 中的 CREATE TABLE 语句
            return $"CREATE TABLE {tableName} (\n    {string.Join(",\n    ", columnDefinitions)}\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
        }

        private static string GenerateCreateTableSQL2(string tableName, List<string> columnNames, List<string> columnTypes)
        {
            string createTableSQL = $"CREATE TABLE {tableName} (";

            for (int i = 0; i < columnNames.Count; i++)
            {
                string columnName = columnNames[i];
                string columnType = columnTypes[i].ToUpper();

                // 处理不同的列类型
                switch (columnType)
                {
                    case "TINYINT":
                    case "SMALLINT":
                    case "MEDIUMINT":
                    case "INT":
                    case "BIGINT":
                        createTableSQL += $"{columnName} {columnType}({(columnType == "TINYINT" ? "1" : "11")}), "; // 默认长度设置为11
                        break;

                    case "FLOAT":
                    case "DOUBLE":
                    case "DECIMAL":
                        createTableSQL += $"{columnName} {columnType}(10, 2), "; // 默认设置为 10, 2
                        break;

                    case "CHAR":
                    case "VARCHAR":
                        createTableSQL += $"{columnName} {columnType}(255), "; // 默认长度设置为 255
                        break;

                    case "TEXT":
                    case "TINYTEXT":
                    case "MEDIUMTEXT":
                    case "LONGTEXT":
                        createTableSQL += $"{columnName} {columnType}, ";
                        break;

                    case "BLOB":
                    case "TINYBLOB":
                    case "MEDIUMBLOB":
                    case "LONGBLOB":
                        createTableSQL += $"{columnName} {columnType}, ";
                        break;

                    case "ENUM":
                        // 默认的 ENUM 类型选项
                        createTableSQL += $"{columnName} {columnType}('value1', 'value2', 'value3'), ";
                        break;

                    case "SET":
                        // 默认的 SET 类型选项
                        createTableSQL += $"{columnName} {columnType}('value1', 'value2', 'value3'), ";
                        break;

                    case "DATE":
                    case "DATETIME":
                    case "TIMESTAMP":
                    case "TIME":
                    case "YEAR":
                        createTableSQL += $"{columnName} {columnType}, ";
                        break;

                    case "BOOLEAN":
                        createTableSQL += $"{columnName} TINYINT(1), "; // MySQL 用 TINYINT(1) 表示 BOOLEAN
                        break;

                    case "BINARY":
                    case "VARBINARY":
                        createTableSQL += $"{columnName} {columnType}(255), "; // 默认长度设置为 255
                        break;

                    case "JSON":
                        createTableSQL += $"{columnName} {columnType}, ";
                        break;

                    // 如果是其他类型，默认为 varchar
                    default:
                        createTableSQL += $"{columnName} VARCHAR(255), ";
                        break;
                }
            }

            // 移除最后一个多余的逗号
            createTableSQL = createTableSQL.TrimEnd(',', ' ') + ");";

            return createTableSQL;
        }

        // 获取表的所有列名
        private static List<string> GetColumnNames(string connectionString, string tableName)
        {
            List<string> columnNames = new List<string>();
            var dal = DAL.Create(Project.Meta.ConnName);
            string query = $"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{tableName}' and TABLE_SCHEMA = '{connectionString}'";
            var reader = dal.ExecuteReader(query);
            while (reader.Read())
            {
                columnNames.Add(reader.GetString(0));
            }
            return columnNames;
        }

        // 获取源数据库中的数据
        private static List<List<object>> GetDataFromSource(string connectionString, string query)
        {
            List<List<object>> rows = new List<List<object>>();
            var dal = DAL.Create(Project.Meta.ConnName);
            // string query = $"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{tableName}'";
            var reader = dal.ExecuteReader(query);
            while (reader.Read())
            {
                List<object> row = new List<object>();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    row.Add(reader.GetValue(i));
                }
                rows.Add(row);
            }

            return rows;
        }

        // 生成插入备份数据库的 SQL 语句
        private static string GenerateInsertQuery(string tableName, List<string> columnNames, List<List<object>> rows)
        {
            string insertQuery = $"INSERT INTO {tableName} ({string.Join(", ", columnNames)}) VALUES ";

            foreach (var row in rows)
            {
                List<string> formattedValues = new List<string>();

                for (int i = 0; i < row.Count; i++)
                {
                    var value = row[i];
                    string formattedValue;
                    // 如果是日期时间类型（DATETIME, TIMESTAMP）
                    if (value is DateTime dateTimeValue)
                    {
                        formattedValue = $"'{dateTimeValue:yyyy-MM-dd HH:mm:ss}'";
                    }
                    // 如果是布尔类型
                    else if (value is bool boolValue)
                    {
                        formattedValue = boolValue ? "1" : "0";
                    }
                    // 如果是字符串类型
                    else if (value is string strValue)
                    {
                        // 转义单引号并处理 UTF-8 编码
                        formattedValue = $"'{strValue.Replace("'", "''").Replace("\\", "\\\\")}'"; // 转义单引号和反斜杠
                    }
                    // 如果是数值类型
                    else if (value is int || value is long || value is float || value is double || value is decimal)
                    {
                        formattedValue = value.ToString(); // 不需要单引号
                    }
                    // 如果是 NULL 值
                    else if (value == null || value.ToString() == "")
                    {
                        formattedValue = "NULL";
                    }
                    else
                    {
                        // 其他类型的值直接转换为字符串
                        formattedValue = $"'{value.ToString().Replace("'", "''").Replace("\\", "\\\\")}'"; // 转义单引号和反斜杠 ;
                    }

                    formattedValues.Add(formattedValue);
                }

                // 拼接每一行的插入数据
                insertQuery += $"({string.Join(", ", formattedValues)}),";
            }

            // 移除最后一个多余的逗号
            insertQuery = insertQuery.TrimEnd(',');

            return insertQuery + ";";
        }

        /// <summary>
        /// 创建压缩文件
        /// </summary>
        /// <param name="filesPath"></param>
        /// <param name="zipFilePath"></param>
        public static void CreateZipFile(string filesPath, string zipFilePath)
        {
            if (!Directory.Exists(filesPath))
            {
                Console.WriteLine("Cannot find directory '{0}'", filesPath);
                return;
            }
            string[] filenames = Directory.GetFiles(filesPath);
            using (ZipOutputStream s = new ZipOutputStream(File.Create(zipFilePath)))
            {
                s.SetLevel(9); // 压缩级别 0-9
                byte[] buffer = new byte[4096]; //缓冲区大小
                foreach (string file in filenames)
                {
                    ZipEntry entry = new ZipEntry(Path.GetFileName(file));
                    entry.DateTime = DateTime.Now;
                    s.PutNextEntry(entry);
                    using (FileStream fs = File.OpenRead(file))
                    {
                        int sourceBytes;
                        do
                        {
                            sourceBytes = fs.Read(buffer, 0, buffer.Length);
                            s.Write(buffer, 0, sourceBytes);
                        } while (sourceBytes > 0);
                    }
                }
                s.Finish();
                s.Close();
            }
        }
    }
}